package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.model.entity.Follow;
import cn.gson.crm.vo.FollowVo;

public class FollowDao {
	/**
	 * 根据登录人ID查询所有需要跟进情况
	 * @return
	 * @throws SQLException
	 */
	public List<FollowVo> getAllFollowInfo(int id) throws SQLException{
		String sql = "select f.*,w.way_name,c.cust_name,e.emp_name from follow f inner join way w on f.follow_way_id = w.way_id " +
						"inner join customer c on f.follow_custname_id = c.cust_id " + 
						"inner join employee e on f.follow_emp_id = e.emp_id " + 
						"where follow_emp_id = ?  and follow_data in (select max(follow_data) from follow group by follow_custname_id) " + 
						"group by f.follow_custname_id ORDER BY follow_data desc ";
		List<Map<String,Object>> map = Jdbc.getInstace().executeQueryForList(sql,id);
		List<FollowVo> list = new ArrayList<>();
		for (Map<String,Object> mm : map) {
			FollowVo fv = new FollowVo();
			fv.setFollow_id(Integer.parseInt(mm.get("follow_id").toString()));
			fv.setFollow_custname_id(Integer.parseInt(mm.get("follow_custname_id").toString()));
			fv.setFollow_data(Timestamp.valueOf(mm.get("follow_data").toString()));
			fv.setFollow_situation((String)mm.get("follow_situation"));
			fv.setFollow_way_id(Integer.parseInt(mm.get("follow_way_id").toString()));
			fv.setFollow_times(Integer.parseInt(mm.get("follow_times").toString()));
			fv.setWay_name((String)mm.get("way_name"));
			fv.setCust_name((String)mm.get("cust_name"));
			fv.setFollow_emp_id(Integer.parseInt(mm.get("follow_emp_id").toString()));
			fv.setEmp_name((String)mm.get("emp_name"));
			
			list.add(fv);
		}
		return list;
	}
	
	/**
	 * 新增跟进信息
	 * @param f
	 * @return
	 * @throws SQLException
	 */
	public boolean insertFollowInfo(Follow f) throws SQLException{
		String sql = "insert into Follow(follow_custname_id,follow_situation,follow_way_id,follow_times,follow_emp_id) values(?,?,?,?,?)";
		return Jdbc.getInstace().executeUpdate(sql, f.getFollow_custname_id(),f.getFollow_situation(),f.getFollow_way_id(),1,f.getFollow_emp_id());
	}
	
	/**
	 * 根据客户ID查询所有跟进记录
	 * @param custId
	 * @return
	 * @throws SQLException
	 */
	public List<FollowVo> getAllFollowInfoByCustomerId(int custId) throws SQLException{
		String sql = "select follow_data,follow_situation,way_name,e.emp_name from follow f inner join way w on w.way_id = f.follow_way_id " +
						"inner join employee e on f.follow_emp_id = e.emp_id " + 
							"where follow_custname_id = ? order by follow_data desc";
		List<Map<String,Object>> map = Jdbc.getInstace().executeQueryForList(sql, custId);
		List<FollowVo> list = new ArrayList<>();
		for (Map<String,Object> mm : map) {
			FollowVo f = new FollowVo();
			f.setFollow_data(Timestamp.valueOf(mm.get("follow_data").toString()));
			f.setFollow_situation(mm.get("follow_situation").toString());
			f.setWay_name(mm.get("way_name").toString());
			f.setEmp_name(mm.get("emp_name").toString());
			
			list.add(f);
		}
		return list;
	}
	
	/**
	 * 根据客户ID查询总记录数
	 * @param custId
	 * @return
	 * @throws SQLException
	 */
	public Long getTotalTimesByCustId(int custId) throws SQLException{
		String sql = "select count(follow_times) times from follow where follow_custname_id = ?";
		Map<String,Object> map = Jdbc.getInstace().executeQuery(sql, custId);
		Long times = Long.valueOf(map.get("times").toString());
		return times;
	}
}
